<html>
    <head>
        <title>- CSV Customer Import -</title>
    </head>
    <body>
        <form>
            <input type="file" name="F1" id="F1" size="80"><input type="button" value="Read" name="B1" id="B1" onclick="execFile()"><input type="reset" value="Reset" name="B2" id="B2" onclick="renderArea.innerHTML=''">
        </form>
        <div id="renderArea">
        </div>
        <script type="text/javascript" src="gears_init.js">
        </script>
        <script>
            // -- CSV PARSER --
            // author&nbsp; : Purbayu, 30Sep2008
            // email&nbsp;&nbsp; : purbayubudi@gmail.com
            //
            // description :
            //&nbsp; This jscript code describes how to load csv file and parse it into fields.
            //&nbsp; Additionally, a function to display html table as result is added.
            //
            // disclamer:
            //&nbsp; To use this code freely, you must put author's name in it.
            var db;
            //LoadXPIE();
            init();
            var renderArea = document.getElementById("renderArea");
            
            function execFile(){
                // main function to open, parse, and then render
                var myfile = document.getElementById("F1");
                
                // verify file extension (csv or not)
                if (myfile.value.match(/\.csv$/gi) == ".csv") {
                    // create progress window..
                    
                    /*
                     var progressWindow = window.open("","","top=10,left=10,height=100,width=200");
                     progressWindow.document.write("<html><head></head><body><div id='progressArea'></div></body></html>");
                     var progressArea = progressWindow.document.getElementById("progressArea");
                     */
                    // load csv file and split it line by line
                    var arr = readCSV(myfile.value);
                    
                    // parse csv line by line, start at 1 to skip header
                    for (var i = 1; i < arr.length; i++) {
                        arr[i] = parseLineCSV(arr[i]);
                        renderArea.innerHTML = "Status: " + i + "/" + arr.length;
                        //progressArea.innerHTML="Parsing: "+(i+1)+" of "+arr.length;
                    }
                    return;
                    // render the result into html table
                    s = '<table border=1>';
                    for (var i = 0; i < arr.length; i++) {
                        s = s + '<tr>';
                        for (var j = 0; j < arr[i].length; j++) {
                            s = s + '<td><font size="1" face="Verdana">' + arr[i][j] + '</font></td>';
                        }
                        //progressArea.innerHTML="Table rendering: "+(i+1)+" of "+arr.length;
                        s = s + '</tr>';
                    }
                    s = s + '</table>';
                    
                    // close progress window after all tasks are completed
                    // progressWindow.close();
                }
                else {
                    // show this if user tries to open non csv file
                    s = '<b><font size="1" face="Verdana" color="#FF0000">Not a CSV file!</font></b>';
                }
                
                renderArea.innerHTML = s;
            }
            
            //The following code is depicted from Professional Javascript for
            // Web Developers, a book by Nicholas C.Zakas
            /*
             if (typeof XMLHttpRequest == "undefined" && window.ActiveXObject) {
             function XMLHttpRequest() {
             var arrSignatures = ["MSXML2.XMLHTTP.5.0", "MSXML2.XMLHTTP.4.0",
             "MSXML2.XMLHTTP.3.0", "MSXML2.XMLHTTP",
             "Microsoft.XMLHTTP"];
             for (var i=0; i < arrSignatures.length; i++) {
             try {
             var oRequest = new ActiveXObject(arrSignatures[i]);
             return oRequest;
             } catch (oError) {
             //ignore
             }
             }
             throw new Error("MSXML is not installed on your system.");
             }
             }
             */
            function init(){
                var success = false;
                
                if (window.google && google.gears) {
                    try {
                        db = google.gears.factory.create('beta.database');
                        
                        if (db) {
                            db.open('psdb');
                        }
                        
                    } 
                    catch (ex) {
                    }
                }
            }
            
            function readCSV(locfile){
                // load a whole csv file, and then split it line by line
                
                if (window.XMLHttpRequest) {
                    xhttp = new XMLHttpRequest();
                }
                else {
                    xhttp = new ActiveXObject("Microsoft.XMLHTTP");
                }
                xhttp.open("GET", locfile, false);
                
                xhttp.send("");
                xmlDoc = xhttp.responseXML;
                
                /*
                 if (window.XMLHttpRequest)
                 req = new XMLHttpRequest();
                 alert('here');//req.responseXML);
                 req.open("GET",locfile,false);
                 req.send("");*/
                return xhttp.responseText.split(/\n/g);
            }
            
            function parseLineCSV(lineCSV){
                // parse csv line by line into array
                var CSV = new Array();
                
                // Insert space before character ",". This is to anticipate 'split' in IE
                // try this:
                //
                // var a=",,,a,,b,,c,,,,d";
                // a=a.split(/\,/g);
                // document.write(a.length);
                //
                // You will see unexpected result!
                //
                
                //lineCSV = lineCSV.replace(/,/g," ,");
                
                lineCSV = lineCSV.split(/;/g);
                //column ordinal, do something
                var column = 0;
                var customerid = lineCSV[0];
                var firstname = lineCSV[1];
                var lastname = lineCSV[2];
                var middlename = "";
                var phone = lineCSV[7];
                var phone2 = lineCSV[8];
                var email = lineCSV[9];
                var address1 = lineCSV[3];
                var address2 = lineCSV[4];
                
                var zipcode = lineCSV[5]; //+ lineCSV[6];
				var city = LoadCity(zipcode);
				//if(length(lineCSV[6])>0)
				//	zipcode+="-"+lineCSV[6];
					
                var birthdate = "";
                var balance = lineCSV[10];
                var creditlimit = 100;
                    // 0     1   2      3         4       5      6    7     8      9     10      11
                //ClientID;Name;Last;Address1;Address2;ZipCode;Zip4;Phone;Mobile;Email;Balance;LastPay
               db.execute('insert into Customer (id, firstname, middlename, lastname, phone, phone2, email, address1, address2, city, zipcode, birthdate, balance, creditlimit) values (? ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
			    [customerid, firstname, middlename, lastname, phone, phone2, email, address1, address2, city, zipcode, birthdate, balance, creditlimit]); 
                
                return CSV;
            }
			
			 //loads/imports xml data
                        function LoadCity(zipcode){
                            if (window.XMLHttpRequest) {
                                xhttp2 = new XMLHttpRequest()
                            }
                            else {
                                xhttp2 = new ActiveXObject("Microsoft.XMLHTTP")
                            }
                            
                            xhttp2.open("GET", "cities.xml", false);
                            xhttp2.send("");
                            xmlDoc2 = xhttp2.responseXML;
                            
                            var y = xmlDoc2.getElementsByTagName("cities");
                            for (i = 0; i < y.length; i++) {
                                x = y[i].childNodes; //a specific city
                                if (x[1].childNodes[0].nodeValue == zipcode) //check if matches an id
                                {
                                    return x[3].childNodes[0].nodeValue;
                                }
                            }
                        }
        </script>
    </body>
</html>
